package com.example.demo.mapper;

import com.example.demo.model.Torrent;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface TorrentMapper {
    @Select("<script>" +
            "SELECT * FROM torrent " +
            "<where>" +
            "   <if test='tag != null'>" +
            "       AND tags LIKE CONCAT('%', #{tag}, '%')" +
            "   </if>" +
            "</where>" +
            "ORDER BY ${sortField} ${sortDirection} " +
            "LIMIT #{pageSize} OFFSET #{offset}" +
            "</script>")
    @Results({ /* 与原有@Results一致 */ })
    List<Torrent> searchByTag(
            @Param("tag") String tag,
            @Param("sortField") String sortField,
            @Param("sortDirection") String sortDirection,
            @Param("pageSize") int pageSize,
            @Param("offset") int offset
    );

    // 获取下载量最高的资源（关联TorrentUsage表）
    @Select("SELECT t.* FROM torrent t " +
            "JOIN torrent_usage_stats u ON t.id = u.torrent_id " +
            "ORDER BY u.download_count DESC " +
            "LIMIT #{limit}")
    @Results({
            @Result(property = "coverImage", column = "cover_image"),
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers")})
    List<Torrent> findTopDownloaded(@Param("limit") int limit);

    // 获取最新上传资源
    @Select("SELECT * FROM torrent ORDER BY create_time DESC LIMIT #{limit}")
    @Results({
            @Result(property = "coverImage", column = "cover_image"),
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers")})
    List<Torrent> findLatest(@Param("limit") int limit);

    @Select("SELECT * FROM torrent WHERE id = #{id}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers"),
            @Result(property = "coverImage", column = "cover_image"),
            @Result(property = "type", column = "type"),
            @Result(property = "firstType", column = "first_type"),
            @Result(property = "secondType", column = "second_type"),
            @Result(property = "thirdType", column = "third_type"),
            @Result(property = "fourthType", column = "fourth_type"),
    })
    Torrent findById(long id);

//    @Insert("INSERT INTO torrent (file_name, file_size, info_hash, uploader_id, create_time, seeders, leechers) " +
//            "VALUES (#{fileName}, #{fileSize}, #{infoHash}, #{uploaderId}, #{createTime}, #{seeders}, #{leechers})")
//    @Options(useGeneratedKeys = true, keyProperty = "id")
//    void insertTorrent(Torrent torrent);
    @Insert("INSERT INTO torrent (" +
            "file_name, file_size, info_hash, uploader_id, create_time, " +
            "seeders, leechers, name, type, first_type, second_type, " +
            "third_type, fourth_type, description, cost, cover_image, tags, score" +
            ") VALUES (" +
            "#{fileName}, #{fileSize}, #{infoHash}, #{uploaderId}, #{createTime}, " +
            "#{seeders}, #{leechers}, #{name}, #{type}, #{firstType}, #{secondType}, " +
            "#{thirdType}, #{fourthType}, #{description}, #{cost}, #{coverImage}, #{tags}, #{score}" +
            ")")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insertTorrent(Torrent torrent);

    @Select("SELECT * FROM torrent WHERE info_hash = #{infoHash}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers")
    })
    Torrent findByInfoHash(String infoHash);

    //todo: need to debug this method
    @Update("UPDATE torrent SET seeders = #{seeders}, leechers = #{leechers} WHERE id = #{id}")
    void updatePeerStats(Torrent torrent);

    @Select("SELECT * FROM torrent")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers"),
            @Result(property = "name", column = "name.3"),
            @Result(property = "type", column = "type"),
            @Result(property = "firstType", column = "first_type"),
            @Result(property = "secondType", column = "second_type"),
            @Result(property = "thirdType", column = "third_type"),
            @Result(property = "fourthType", column = "fourth_type"),
            @Result(property = "description", column = "description"),
            @Result(property = "cost", column = "cost"),
            @Result(property = "coverImage", column = "cover_image")
    })
    List<Torrent> findAll();

//    @Update("UPDATE torrents SET seeders = #{seeders}, leechers = #{leechers} WHERE info_hash = #{infoHash}")
//    void updatePeerStats(@Param("infoHash") String infoHash,
//                         @Param("seeders") int seeders,
//                         @Param("leechers") int leechers);

    @Delete("DELETE FROM torrent WHERE id = #{id}")
    void deleteById(Long id);

    // 新增搜索方法
    @Select("<script>" +
            "SELECT * FROM torrent " +
            "<where>" +
            "   <if test='query != null and query != \"\"'>" +
            "       AND file_name LIKE CONCAT('%', #{query}, '%')" +
            "   </if>" +
            "</where>" +
            "ORDER BY ${sortField} ${sortDirection} " +
            "LIMIT #{pageSize} OFFSET #{offset}" +
            "</script>")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers")
    })
    List<Torrent> searchTorrents(
            @Param("query") String query,
            @Param("sortField") String sortField,
            @Param("sortDirection") String sortDirection,
            @Param("pageSize") int pageSize,
            @Param("offset") int offset);

    // 新增计数方法
    @Select("<script>" +
            "SELECT COUNT(*) FROM torrent " +
            "<where>" +
            "   <if test='query != null and query != \"\"'>" +
            "       AND file_name LIKE CONCAT('%', #{query}, '%')" +
            "   </if>" +
            "</where>" +
            "</script>")
    int countSearchResults(@Param("query") String query);

    @Update("UPDATE torrent SET is_promotion_free = true, original_score = score, score = 0 " +
            "WHERE id = #{torrentId}")
    int activatePromotion(@Param("torrentId") Long torrentId);

    @Update("UPDATE torrent SET is_promotion_free = false, score = original_score " +
            "WHERE id = #{torrentId}")
    int deactivatePromotion(@Param("torrentId") Long torrentId);

    @Update("UPDATE torrent SET promotion_start_time = #{startTime}, promotion_end_time = #{endTime} " +
            "WHERE id = #{torrentId}")
    void setPromotionTime(@Param("torrentId") Long torrentId,
                         @Param("startTime") LocalDateTime startTime,
                         @Param("endTime") LocalDateTime endTime);

    @Update("UPDATE torrent SET promotion_start_time = null, promotion_end_time = null " +
            "WHERE id = #{torrentId}")
    void clearPromotionTime(@Param("torrentId")Long torrentId);

    @Select("SELECT id, file_name, promotion_start_time, promotion_end_time " +
            "FROM torrent WHERE is_promotion_free = true AND " +
            "promotion_start_time <= NOW() AND promotion_end_time >= NOW()")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "promotionStartTime", column = "promotion_start_time", javaType = LocalDateTime.class),
            @Result(property = "promotionEndTime", column = "promotion_end_time", javaType = LocalDateTime.class)
    })
    List<Torrent> findActivePromotions();

    @Update("UPDATE torrent SET is_promotion_free = true, " +
            "original_score = CASE WHEN original_score IS NULL THEN score ELSE original_score END, " +
            "score = 0 " +
            "WHERE promotion_start_time <= #{now} " +
            "AND promotion_end_time >= #{now} " +
            "AND is_promotion_free = false")
    int activateExpiredPromotions(@Param("now") LocalDateTime now);

    @Update("UPDATE torrent SET is_promotion_free = false, " +
            "score = CASE WHEN original_score IS NULL THEN 50 ELSE original_score END " +
            "WHERE (promotion_end_time < #{now} OR promotion_start_time IS NULL) " +
            "AND is_promotion_free = true")
    int deactivateExpiredPromotions(@Param("now") LocalDateTime now);

    @Select("SELECT id, file_name, file_size, info_hash, uploader_id, create_time, seeders, leechers, score, " +
            "is_promotion_free, original_score, promotion_start_time, promotion_end_time, status, reviewed_at " +
            "FROM torrent WHERE status = #{status} ORDER BY create_time DESC")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers"),
            @Result(property = "status", column = "status"),
            @Result(property = "reviewedAt",column = "reviewed_at"),
            @Result(property = "score", column = "score"),
            @Result(property = "promotionFree", column = "is_promotion_free"),
            @Result(property = "originalScore", column = "original_score"),
            @Result(property = "promotionStartTime", column = "promotion_start_time"),
            @Result(property = "promotionEndTime", column = "promotion_end_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "reviewedAt", column = "reviewed_at")
    })
    List<Torrent> findByStatus(Integer status);

    @Select("SELECT * FROM torrent WHERE status = #{status} ORDER BY create_time DESC")
    @Results({
            // 复用现有 @Results 映射
            @Result(property = "id", column = "id"),
            @Result(property = "fileName", column = "file_name"),
            @Result(property = "fileSize", column = "file_size"),
            @Result(property = "infoHash", column = "info_hash"),
            @Result(property = "uploaderId", column = "uploader_id"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "seeders", column = "seeders"),
            @Result(property = "leechers", column = "leechers"),
            @Result(property = "score", column = "score"),
            @Result(property = "promotionFree", column = "is_promotion_free"),
            @Result(property = "originalScore", column = "original_score"),
            @Result(property = "promotionStartTime", column = "promotion_start_time"),
            @Result(property = "promotionEndTime", column = "promotion_end_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "reviewedAt", column = "reviewed_at"),
            @Result(property = "reviewedBy", column = "reviewed_by")
    })
    List<Torrent> findPendingTorrents(@Param("status") Integer status);

    @Update("UPDATE torrent SET status = #{status}, reviewed_at = #{reviewedAt}, reviewed_by = #{reviewedBy} WHERE id = #{id}")
    int updateReviewStatus(@Param("id") Long id, @Param("status") Integer status, @Param("reviewedAt") LocalDateTime reviewedAt, @Param("reviewedBy") Long reviewedBy);

    @Update("UPDATE torrent SET " +
            "name = #{name}, " +
            "description = #{description}, " +
            "type = #{type}, " +
            "first_type = #{firstType}, " +
            "second_type = #{secondType}, " +
            "third_type = #{thirdType}, " +
            "fourth_type = #{fourthType}, " +
            "cost = #{cost}, " +
            "cover_image = #{coverImage}, " +
            "tags = #{tags} " +
            "WHERE id = #{id}")
    void updateTorrentMetadata(Torrent torrent);
}